IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_MediaSave]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_MediaSave]
GO
CREATE PROCEDURE [dbo].[cms_sp_MediaSave]
	@Id INT,
	@SiteId INT = NULL,
	@SiteGroupId INT = NULL,
	@FolderName NVARCHAR(250),
	@Description NVARCHAR(500) = NULL,
	@LastUpdatedBy INT,
	@DateLastUpdated DATETIME,
	@ParentMediaId INT = NULL
AS
	UPDATE dbo.Media
	SET    SiteId = @SiteId
	      ,SiteGroupId = @SiteGroupId
	      ,FolderName = @FolderName
	      ,[Description] = @Description
	      ,LastUpdatedBy = @LastUpdatedBy
	      ,DateLastUpdated = @DateLastUpdated
	      ,ParentMediaId = @ParentMediaId
	WHERE  MediaId = @Id
	
	DECLARE @MediaTemp TABLE (Id INT);
	
	WITH Direct(MediaId ,ParentMediaId) AS 
	     (
	         SELECT m.MediaId
	               ,m.ParentMediaId
	         FROM   Media m
	         WHERE  m.MediaId = @Id
	         UNION ALL
	         SELECT m.MediaId
	               ,m.ParentMediaId
	         FROM   Media m
	                INNER JOIN Direct AS d
	                     ON  d.MediaId = m.ParentMediaId
	     )
	
	INSERT INTO @MediaTemp
	SELECT d.MediaId
	FROM   Direct d
	
	-- update media
	UPDATE Media
	SET    SiteId = @SiteId
	      ,SiteGroupId = @SiteGroupId
	FROM   Media
	       INNER JOIN @MediaTemp mt
	            ON  MediaId = mt.Id
	-- update media contents            
	UPDATE MediaContents
	SET    SiteId = @SiteId
	      ,SiteGroupId = @SiteGroupId
	FROM   MediaContents mc
	       INNER JOIN @MediaTemp mt
	            ON  mc.MediaId = mt.Id
	
	-- update media files
	UPDATE MediaFiles
	SET    SiteId          = @SiteId
	      ,SiteGroupId     = @SiteGroupId
	FROM   MediaFiles mf
	       JOIN MediaContentFileLinks mcfl
	            ON  mcfl.MediaFileId = mf.MediaFileId
	       JOIN MediaContents mc
	            ON  mc.MediaContentId = mcfl.MediaContentId
	                AND mc.MediaId IN (SELECT Id
	                                   FROM   @MediaTemp)
GO